/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DatabaseConnection;

import Entities.Route;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author serhat
 */
public class RouteDbConnection extends Connector {

    public RouteDbConnection() throws ClassNotFoundException,
            SQLException, InstantiationException, IllegalAccessException {
    }

    private String buildInsertQuery(Route route) {
        String query = String.format(
                "INSERT INTO Sefer VALUES("
                + "NULL, '%s', '%s', '%s')",
                route.getStart(),
                route.getEnd(),
                route.getPlaka());
        System.out.println(query);
        return query;
    }

    
    
    public int executeUpdate(String query) throws SQLException {
        int result = 0;
        statement = connection.createStatement();

        statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);

        ResultSet rs = statement.getGeneratedKeys();
        if (rs.next()) {
            result = rs.getInt(1);
        }

        return result;

    }

    public int insert(Route route) {

        try {
            String query = buildInsertQuery(route);
            return executeUpdate(query);

        } catch (SQLException ex) {
            Logger.getLogger(RouteDbConnection.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }

    }

    public String buildSelectQuery(Route route) {
        String query = String.format("select id, otobus from"
                + " Sefer where id in (select sefer from Durak where ad = '%s' and tarih = '%s'"
                + ") and id in (select "
                + "sefer from Durak where ad = '%s'"
                + ")",
                route.getStart(),
                route.getDate(),
                route.getEnd());
        System.out.println(query);
        return query;
    }
    
    public ResultSet select(Route route){
        try {
            String query = buildSelectQuery(route);
            ResultSet rs = executeQuery(query);
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(RouteDbConnection.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
    
    public ResultSet selectRuteTime(String id, String start){
        try {
            String query = "SELECT saat FROM `Durak`"
                         + "WHERE sefer ='"+ id +"'"
                         +"AND ad = '" + start + "'";
            System.out.println(query);
            ResultSet rs = executeQuery(query);
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(RouteDbConnection.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
    
    public Float selectRuteTicketFee(String start, String end){
        try {
            String query = "SELECT ucret from Ucret"
                         + " WHERE kalkis ='"+ start +"' "
                         +"AND varis = '" + end + "'";
            System.out.println(query);
            ResultSet rs = executeQuery(query);
            rs.next();
            
            return Float.valueOf(rs.getString(1));
            
        } catch (SQLException ex) {
            Logger.getLogger(RouteDbConnection.class.getName()).log(Level.SEVERE, null, ex);
            return 0f;
        }
    }
    
    
}
